package com.webwork.mood.record.dao.impl;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.webwork.mood.common.dao.impl.SimpleDao;
import com.webwork.mood.record.dao.RecordDao;
import com.webwork.mood.record.dataobject.MoodAverage;
import com.webwork.mood.record.dataobject.MoodRecord;
import com.webwork.mood.record.dataobject.MoodScore;
import com.webwork.mood.util.ColumnLimit;
import com.webwork.mood.util.DBSimpleSQL;
import com.webwork.mood.util.StrManager;

@Repository
public class RecordDaoImpl extends SimpleDao implements RecordDao {

	public RecordDaoImpl() {
		super();
		this.dbTable = "t_score";
		dbColumns.clear();
		dbColumns.put("recordId", "RECORD_ID");
		dbColumns.put("userId", "USER_ID");
		dbColumns.put("weixinId", "WEIXIN_ID");
		dbColumns.put("opTime", "OP_TIME");
		dbColumns.put("score", "SCORE");
		dbColumns.put("comment", "COMMENT");
	}
	
	@Override
	public ColumnLimit condition(Object object) {
		if (!(object instanceof MoodRecord))
			return new ColumnLimit();
		MoodRecord o = (MoodRecord) object;
		ColumnLimit ret = new ColumnLimit();
		if (!StrManager.isEmpty(o.getRecordId())) {
			ret.add(dbColumns.get("recordId") + " like ");
		}
		if (!StrManager.isEmpty(o.getUserId())) {
			ret.add(dbColumns.get("userId") + " = ");
		}
		if (!StrManager.isEmpty(o.getWeixinId())) {
			ret.add(dbColumns.get("weixinId") + " = ");
		}
		if (o.getRecordDate() != null) {
			ret.add(dbColumns.get("opTime") + " = ");
		}
		return ret;
	}

	@Override
	public Object[] statement(Object object) {
		MoodRecord o = (MoodRecord) object;
		List<Object> ret = new ArrayList<Object>();
		if (!StrManager.isEmpty(o.getRecordId())) {
			ret.add("%" + o.getRecordId() + "%");
		}
		if (!StrManager.isEmpty(o.getUserId())) {
			ret.add(o.getUserId());
		}
		if (!StrManager.isEmpty(o.getWeixinId())) {
			ret.add(o.getWeixinId());
		}
		if (o.getRecordDate() != null) {
			ret.add(o.getRecordDate());
		}
		return ret.toArray();
	}

	@Override
	public List<Object> pushObject(Object object) {
		MoodRecord o = (MoodRecord) object;
		List<Object> objs = new ArrayList<Object>();
		objs.add(o.getRecordId());
		objs.add(o.getUserId());
		objs.add(o.getWeixinId());
		objs.add(o.getRecordDate());
		objs.add(o.getScore());
		objs.add(o.getComment());
		return objs;
	}

	@Override
	public Object catchObject(Map<String, Object> map) {
		MoodRecord ret = new MoodRecord();
		ret.setRecordId((String) map.get(dbColumns.get("recordId")));
		ret.setUserId((String) map.get(dbColumns.get("userId")));
		ret.setWeixinId((String) map.get(dbColumns.get("weixinId")));
		ret.setRecordDate((Timestamp) map.get(dbColumns.get("opTime")));
		ret.setScore((Integer) map.get(dbColumns.get("score")));
		ret.setComment((String) map.get(dbColumns.get("comment")));
		return ret;
	}
	
	@Override
	public void insert(MoodRecord o) {
		String sql = DBSimpleSQL.insert(dbTable, dbColumns.size());
		this.getJdbcTemplate().update(sql, this.pushObject(o).toArray());
	}

	@Override
	public void update(MoodRecord o) {
		ColumnLimit limit = new ColumnLimit();
		limit.add(dbColumns.get("recordId") + " = ");
		String sql = DBSimpleSQL.update(dbTable, dbColumns, limit);
		List<Object> objs = this.pushObject(o);
		objs.add(o.getRecordId());
		this.getJdbcTemplate().update(sql, objs.toArray());
	}

	@Override
	public void delete(String recordId) {
		ColumnLimit limit = new ColumnLimit();
		limit.add(dbColumns.get("recordId") + " = ");
		String sql = DBSimpleSQL.del(dbTable, limit);
		this.getJdbcTemplate().update(sql, recordId);
	}

	@Override
	public List<MoodRecord> query(MoodRecord o, int page, int size) {
		List<MoodRecord> ret = new ArrayList<MoodRecord>();
		String sql = DBSimpleSQL.selectAll(dbTable, this.condition(o), page, size);
		List<Map<String, Object>> maps = this.getJdbcTemplate().queryForList(sql, this.statement(o));
		for (Map<String, Object> map : maps) {
			ret.add((MoodRecord) this.catchObject(map));
		}
		return ret;
	}

	@Override
	public int count(MoodRecord o) {
		String sql = DBSimpleSQL.count(dbTable, this.condition(o));
		return this.getJdbcTemplate().queryForObject(sql, this.statement(o), Integer.class);
	}

	@Override
	public MoodScore getMoodScoreTable(Calendar before, Calendar after) {
		MoodScore ret = new MoodScore();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "SELECT COUNT(SCORE) AS total FROM " + this.dbTable + " where ";
		if (before != null && before.getTimeInMillis() != 0 && after != null && after.getTimeInMillis() != 0) {
			String startStr = df.format(before.getTime());
			String endStr = df.format(after.getTime());
			sql += dbColumns.get("opTime") + " between '" + startStr + "' and '" + endStr + "'";
		}
		Integer total = this.getJdbcTemplate().queryForObject(sql, Integer.class);
		ret.setTotalCount(total);
		
		sql = "SELECT AVG(SCORE) AS average FROM " + this.dbTable + " where ";
		if (before != null && before.getTimeInMillis() != 0 && after != null && after.getTimeInMillis() != 0) {
			String startStr = df.format(before.getTime());
			String endStr = df.format(after.getTime());
			sql += dbColumns.get("opTime") + " between '" + startStr + "' and '" + endStr + "'";
		}
		Float average = this.getJdbcTemplate().queryForObject(sql, Float.class);
		ret.setAverageScore(average);
		
		return ret;
	}
	
	public List<MoodAverage> getMoodScore(Calendar before, Calendar after, int isManager) {
		List<MoodAverage> ret = new ArrayList<MoodAverage>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "SELECT t_score.OP_TIME AS OP_TIME, AVG(t_score.SCORE) AS SCORE FROM " + this.dbTable;
		sql += " INNER JOIN t_user ON t_score.USER_ID = t_user.USER_ID WHERE IS_MANAGER = " + isManager;
		if (before != null && before.getTimeInMillis() != 0 && after != null && after.getTimeInMillis() != 0) {
			String startStr = df.format(before.getTime());
			String endStr = df.format(after.getTime());
			sql += " AND " + dbColumns.get("opTime") + " between '" + startStr + "' and '" + endStr + "'";
		}
		sql += " GROUP BY t_score.OP_TIME";
		List<Map<String, Object>> maps = this.getJdbcTemplate().queryForList(sql);
		for (Map<String, Object> map : maps) {
			MoodAverage mr = new MoodAverage();
//			Date recordDate = (java.sql.Date) map.get(dbColumns.get("opTime"));
			Date recordDate = (Timestamp) map.get(dbColumns.get("opTime"));
			mr.setRecordDate(df.format(recordDate));
			String score = map.get(dbColumns.get("score")).toString();
			mr.setTemp(Float.valueOf(score));
			ret.add(mr);
		}
		return ret;
	}
	
}
